package edu.jptest.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import edu.jptest.bean.Group;
import edu.jptest.util.GetDataBaseConnection;

public class GroupDAO {
	// 插入团体
	public boolean insertGroup(Group group) {
		GetDataBaseConnection getDataBaseConnection = new GetDataBaseConnection();
		Connection connection = getDataBaseConnection.getConnection();
		PreparedStatement pstm = null;
		try {
			String sql = "insert into groups(group_name,class_name,group_teacher) values(?,?,?)";
			pstm = connection.prepareStatement(sql);
			pstm.setString(1, group.getGroupName());
			pstm.setString(2, group.getClassName());
			pstm.setString(3, group.getGroupTeacher());
			int result = pstm.executeUpdate();
			if (result == 1) {
				return true;
			} else {
				return false;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (connection != null)
				try {
					connection.close();
				} catch (SQLException e) {
					;
				}
			connection = null;
			if (pstm != null) {
				try {
					pstm.close();
				} catch (SQLException e) {
					;
				}
			}
			pstm = null;
		}
		return false;
	}

	// 删除团体
	public boolean deleteGroup(int group_id) {
		GetDataBaseConnection getDataBaseConnection = new GetDataBaseConnection();
		Connection connection = getDataBaseConnection.getConnection();
		PreparedStatement pstm = null;
		try {
			String sql = "delete from groups where group_id=?";
			pstm = connection.prepareStatement(sql);
			pstm.setInt(1, group_id);
			int result = pstm.executeUpdate();
			if (result == 1) {
				return true;
			} else {
				return false;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (connection != null)
				try {
					connection.close();
				} catch (SQLException e) {
					;
				}
			connection = null;
			if (pstm != null) {
				try {
					pstm.close();
				} catch (SQLException e) {
					;
				}
			}
			pstm = null;
		}
		return false;
	}

	// 更该团体
	public boolean updateGroup(Group group) {
		GetDataBaseConnection getDataBaseConnection = new GetDataBaseConnection();
		Connection connection = getDataBaseConnection.getConnection();
		PreparedStatement pstm = null;
		try {
			String sql = "update groups set group_name=?,class_name=?,group_teacher=? where group_id=?";
			pstm = connection.prepareStatement(sql);
			pstm.setString(1, group.getGroupName());
			pstm.setString(2, group.getClassName());
			pstm.setString(3, group.getGroupTeacher());
			pstm.setInt(4, group.getGroupId());
			int result = pstm.executeUpdate();
			if (result == 1) {
				return true;
			} else {
				return false;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (connection != null)
				try {
					connection.close();
				} catch (SQLException e) {
					;
				}
			connection = null;
			if (pstm != null) {
				try {
					pstm.close();
				} catch (SQLException e) {
					;
				}
			}
			pstm = null;
		}
		return false;
	}

	// 通过教师姓名，查询团体id
	public List<Integer> getAllGroupsIdByTeacherId(String teacherName) {
		GetDataBaseConnection getDataBaseConnection = new GetDataBaseConnection();
		Connection connection = getDataBaseConnection.getConnection();
		ResultSet resultSet = null;
		PreparedStatement pstm = null;
		List<Integer> results = new ArrayList<Integer>();
		try {
			String sql = "select group_id from groups where group_teacher=?";
			pstm = connection.prepareStatement(sql);
			pstm.setString(1, teacherName);
			resultSet = pstm.executeQuery();
			while (resultSet.next()) {
				results.add(resultSet.getInt(1));
			}
			return results;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (connection != null)
				try {
					connection.close();
				} catch (SQLException e) {
					;
				}
			connection = null;
			if (pstm != null) {
				try {
					pstm.close();
				} catch (SQLException e) {
					;
				}
			}
			pstm = null;
			if (resultSet != null) {
				try {
					resultSet.close();
				} catch (SQLException e) {
					;
				}
			}
			resultSet = null;
		}
		return null;
	}

	// 通过团体id，查询团体对象
	public Group getGroupById(int group_id) {
		GetDataBaseConnection getDataBaseConnection = new GetDataBaseConnection();
		Connection connection = getDataBaseConnection.getConnection();
		ResultSet resultSet = null;
		Group group = new Group();
		PreparedStatement pstm = null;
		try {
			String sql = "select * from groups where group_id=?";
			pstm = connection.prepareStatement(sql);
			pstm.setInt(1, group_id);
			resultSet = pstm.executeQuery();
			while (resultSet.next()) {
				group.setGroupId(resultSet.getInt(1));
				group.setGroupName(resultSet.getString(2));
				group.setClassName(resultSet.getString(3));
				group.setGroupTeacher(resultSet.getString(4));
				return group;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (connection != null)
				try {
					connection.close();
				} catch (SQLException e) {
					;
				}
			connection = null;
			if (pstm != null) {
				try {
					pstm.close();
				} catch (SQLException e) {
					;
				}
			}
			pstm = null;
			if (resultSet != null) {
				try {
					resultSet.close();
				} catch (SQLException e) {
					;
				}
			}
			resultSet = null;
		}
		return new Group();
	}

	// 根据团体名、班级名得到团体ID
	public Integer getGroupIdByName(String groupName, String className) {
		GetDataBaseConnection getDataBaseConnection = new GetDataBaseConnection();
		Connection connection = getDataBaseConnection.getConnection();
		ResultSet resultSet = null;
		PreparedStatement pstm = null;
		int groupId = 0;
		try {
			String sql = "select group_id from groups where group_name=? and class_name=?";
			pstm = connection.prepareStatement(sql);
			pstm.setString(1, groupName);
			pstm.setString(2, className);
			resultSet = pstm.executeQuery();
			while (resultSet.next()) {
				groupId = resultSet.getInt(1);
			}
			return groupId;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (connection != null)
				try {
					connection.close();
				} catch (SQLException e) {
					;
				}
			connection = null;
			if (pstm != null) {
				try {
					pstm.close();
				} catch (SQLException e) {
					;
				}
			}
			pstm = null;
			if (resultSet != null) {
				try {
					resultSet.close();
				} catch (SQLException e) {
					;
				}
			}
			resultSet = null;
		}
		return null;
	}

	// 获得所有团体id
	public List<Integer> getAllGroupsId() {
		GetDataBaseConnection getDataBaseConnection = new GetDataBaseConnection();
		Connection connection = getDataBaseConnection.getConnection();
		ResultSet resultSet = null;
		PreparedStatement pstm = null;
		List<Integer> groupsId = new ArrayList<Integer>();
		try {
			String sql = "select group_id from groups";
			pstm = connection.prepareStatement(sql);
			resultSet = pstm.executeQuery();
			while (resultSet.next()) {
				groupsId.add(resultSet.getInt(1));
			}
			return groupsId;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (connection != null)
				try {
					connection.close();
				} catch (SQLException e) {
					;
				}
			connection = null;
			if (pstm != null) {
				try {
					pstm.close();
				} catch (SQLException e) {
					;
				}
			}
			pstm = null;
			if (resultSet != null) {
				try {
					resultSet.close();
				} catch (SQLException e) {
					;
				}
			}
			resultSet = null;
		}
		return null;
	}
}
